
{***********************************************************}
{                                                           }
{      Informix Data Access Components                      }
{                                                           }
{      TIfxUpdateSQL component editor                       }
{                                                           }
{      Copyright (c) 2001-2008 Luxena Software Company      }
{      http://www.luxena.com                                }
{      e-mail: support@luxena.com                           }
{                                                           }
{***********************************************************}

unit IfxUpdateSQLForm;

{$I lx.inc}

interface

uses
  Classes, DB, ActnList, Controls, Forms, ComCtrls, StdCtrls, ExtCtrls,
  IfxConnection, IfxUpdateSQL;

const
  InsertSQLIndex   = 0;
  UpdateSQLIndex   = 1;
  DeleteSQLIndex   = 2;
  RefreshSQLIndex  = 3;

type

{ TIfxFormUpdateSQL }

  TIfxFormUpdateSQL = class(TForm)
    ActionList: TActionList;
    PageControl: TPageControl;
    buttonOK: TButton;
    buttonCancel: TButton;
    tabsheetOptions: TTabSheet;
    grpboxSQLGeneration: TGroupBox;
    labelTableName: TLabel;
    comboTableName: TComboBox;
    buttonTableDefinitions: TButton;
    buttonDataSetDefaults: TButton;
    buttonGenerateSQL: TButton;
    labelKeyFields: TLabel;
    listboxKeyFields: TListBox;
    labelUpdateFields: TLabel;
    listboxUpdateFields: TListBox;
    tabsheetSQL: TTabSheet;
    radiogrpStatementType: TRadioGroup;
    labelSQLText: TLabel;
    memoSQL: TMemo;
    actionTableDefinitions: TAction;
    actionDataSetDefaults: TAction;
    actionGenerateSQL: TAction;
    procedure comboTableNameDropDown(Sender: TObject);
    procedure actionTableDefinitionsExecute(Sender: TObject);
    procedure actionDataSetDefaultsExecute(Sender: TObject);
    procedure actionGenerateSQLExecute(Sender: TObject);
    procedure radiogrpStatementTypeClick(Sender: TObject);
    procedure memoSQLChange(Sender: TObject);
    procedure ActionListUpdate(Action: TBasicAction; var Handled: Boolean);
  private
    FUpdateSQL: TIfxUpdateSQL;
    FConnection: TIfxConnection;
    FDataSet: TDataSet;
    FSQL: array [InsertSQLIndex..RefreshSQLIndex] of TStrings;
    function GetKeyFields(DataSet: TDataSet): string;
    procedure SetFields(DataSet: TDataSet; const ErrorName: string);
    procedure SetDataSetDefaults;
  public
    class function Edit(UpdateSQL: TIfxUpdateSQL): Boolean;
    constructor Create(UpdateSQL: TIfxUpdateSQL); reintroduce;
    destructor Destroy; override;
    function Run: Boolean;
  end;

  
implementation

uses SysUtils, Dialogs, LXConsts, IfxCustomDataSet, IfxTable;

{$R *.dfm}

resourcestring
  SFieldsUpdateError = 'Unable to determine field names for %s';

  
{ TIfxFormUpdateSQL }

class function TIfxFormUpdateSQL.Edit(UpdateSQL: TIfxUpdateSQL): Boolean;
begin
  with TIfxFormUpdateSQL.Create(UpdateSQL) do
    try
      Result := Run;
    finally
      Free;
    end;
end;

constructor TIfxFormUpdateSQL.Create(UpdateSQL: TIfxUpdateSQL);
begin
  inherited Create(nil);

  FUpdateSQL := UpdateSQL;

  FSQL[InsertSQLIndex] := TStringList.Create;
  FSQL[UpdateSQLIndex] := TStringList.Create;
  FSQL[DeleteSQLIndex] := TStringList.Create;
  FSQL[RefreshSQLIndex] := TStringList.Create;

  if UpdateSQL.Owner <> nil then
    Caption := UpdateSQL.Owner.Name + '.' + UpdateSQL.Name else
    Caption := UpdateSQL.Name;

  if UpdateSQL.DataSet <> nil then
  begin
    comboTableName.Text := IProviderSupport(UpdateSQL.DataSet).PSGetTableName;
    FDataSet := UpdateSQL.DataSet;
    if FDataSet is TIfxCustomDataSet then
      FConnection := TIfxCustomDataSet(FDataSet).Connection;
    SetDataSetDefaults;
  end;

  FSQL[InsertSQLIndex].Assign(UpdateSQL.InsertSQL);
  FSQL[UpdateSQLIndex].Assign(UpdateSQL.ModifySQL);
  FSQL[DeleteSQLIndex].Assign(UpdateSQL.DeleteSQL);
  FSQL[RefreshSQLIndex].Assign(UpdateSQL.RefreshSQL);
end;

destructor TIfxFormUpdateSQL.Destroy;
begin
  FSQL[InsertSQLIndex].Free;
  FSQL[UpdateSQLIndex].Free;
  FSQL[DeleteSQLIndex].Free;
  FSQL[RefreshSQLIndex].Free;
  inherited;
end;

function TIfxFormUpdateSQL.Run: Boolean;
begin
  Result := ShowModal = mrOK;
  if Result then
  begin
    FUpdateSQL.InsertSQL.Assign(FSQL[InsertSQLIndex]);
    FUpdateSQL.ModifySQL.Assign(FSQL[UpdateSQLIndex]);
    FUpdateSQL.DeleteSQL.Assign(FSQL[DeleteSQLIndex]);
    FUpdateSQL.RefreshSQL.Assign(FSQL[RefreshSQLIndex]);
  end;
end;

procedure TIfxFormUpdateSQL.comboTableNameDropDown(Sender: TObject);
begin
  if FConnection <> nil then
    FConnection.GetTableNames(comboTableName.Items);
end;

function TIfxFormUpdateSQL.GetKeyFields(DataSet: TDataSet): string;
var
  I: Integer;
begin
  Result := IProviderSupport(DataSet).PSGetKeyFields;
  if (Result = '') and (DataSet is TIfxTable) then
    with TIfxTable(DataSet) do
    begin
      IndexDefs.Update;
      for I := IndexDefs.Count - 1 downto 0 do
        with IndexDefs[I] do
          if [ixPrimary, ixUnique] * Options <> [] then
          begin
            Result := Fields;
            if ixPrimary in Options then Break;
          end;
    end;
  Result := StringReplace(Result, ';', ',', [rfReplaceAll]);
end;

procedure TIfxFormUpdateSQL.SetFields(DataSet: TDataSet; const ErrorName: string);
var
  I: Integer;
  FieldDefs: TFieldDefs;
  FieldDef: TFieldDef;
  KeyFieldNames: TStringList;
  IsKeyField: Boolean;
begin
  try
    FieldDefs := DataSet.FieldDefs;
    FieldDefs.Update;
    listboxKeyFields.Items.BeginUpdate;
    listboxUpdateFields.Items.BeginUpdate;
    try
      listboxKeyFields.Clear;
      listboxUpdateFields.Clear;
      KeyFieldNames := TStringList.Create;
      try
        KeyFieldNames.CommaText := GetKeyFields(DataSet);
        for I := 0 to FieldDefs.Count - 1 do
        begin
          FieldDef := FieldDefs[I];
          IsKeyField := KeyFieldNames.IndexOf(FieldDef.Name) >= 0;
          with listboxKeyFields do
            Selected[Items.Add(FieldDef.Name)] := IsKeyField;
          with listboxUpdateFields do
            Selected[Items.Add(FieldDef.Name)] := not IsKeyField;
        end;
      finally
        KeyFieldNames.Free;
      end;
    finally
      listboxKeyFields.Items.EndUpdate;
      listboxUpdateFields.Items.EndUpdate;
    end;
  except
    MessageDlg(Format(SFieldsUpdateError, [ErrorName]), mtError, [mbOK], 0);
  end;
end;

procedure TIfxFormUpdateSQL.SetDataSetDefaults;
begin
  SetFields(FDataSet, FDataSet.Name);
end;

procedure TIfxFormUpdateSQL.actionTableDefinitionsExecute(Sender: TObject);
var
  IfxTable: TIfxTable;
begin
  IfxTable := TIfxTable.Create(nil);
  try
    IfxTable.Connection := FConnection;
    IfxTable.TableName := comboTableName.Text;
    SetFields(IfxTable, IfxTable.TableName);
  finally
    IfxTable.Free;
  end;
end;

procedure TIfxFormUpdateSQL.actionDataSetDefaultsExecute(Sender: TObject);
begin
  SetDataSetDefaults;
end;

procedure TIfxFormUpdateSQL.actionGenerateSQLExecute(Sender: TObject);
var
  TableName: string;
  I: Integer;
  FieldNames: TStrings;
  FieldName: string;
  UpdateParam, WhereParam: string;
  InsertFields, InsertParams, InsertSeparator: string;
  SetSQL, SetSQLSeparator: string;
  SelectFields, SelectSeparator: string;
  ModifyWhereSQL, RefreshWhereSQL, WhereSQLSeparator: string;
begin
  TableName := comboTableName.Text;

  FieldNames := listboxKeyFields.Items;
  for I := 0 to FieldNames.Count - 1 do
  begin
    FieldName := FieldNames[I];
    UpdateParam := ':' + FieldName;
    WhereParam := ':OLD_' + FieldName;
    if listboxKeyFields.Selected[I] or listboxUpdateFields.Selected[I] then
    begin
      InsertFields := InsertFields + InsertSeparator + FieldName;
      InsertParams := InsertParams + InsertSeparator + UpdateParam;
      InsertSeparator := ', ';
      if listboxUpdateFields.Selected[I] then
      begin
        SetSQL := SetSQL + SetSQLSeparator + FieldName + ' = ' + UpdateParam;
        SetSQLSeparator := ',' + SLineBreak + '  ';
        SelectFields := SelectFields + SelectSeparator + FieldName;
        SelectSeparator := ', ';
      end;
      if listboxKeyFields.Selected[I] then
      begin
        ModifyWhereSQL := ModifyWhereSQL + WhereSQLSeparator + FieldName + ' = ' + WhereParam;
        RefreshWhereSQL := RefreshWhereSQL + WhereSQLSeparator + FieldName + ' = ' + UpdateParam;
        WhereSQLSeparator := SLineBreak + '  and ';
      end;
    end;
  end;

  FSQL[InsertSQLIndex].Text :=
    'insert into ' + TableName + SLineBreak +
    '  (' + InsertFields + ')' + SLineBreak +
    'values' + SLineBreak +
    '  (' + InsertParams + ')';

  ModifyWhereSQL := SLineBreak + 'where' + SLineBreak + '  ' + ModifyWhereSQL;

  FSQL[UpdateSQLIndex].Text :=
    'update ' + TableName + SLineBreak +
    'set' + SLineBreak +
    '  ' + SetSQL + ModifyWhereSQL;

  FSQL[DeleteSQLIndex].Text := 'delete from ' + TableName + ModifyWhereSQL;

  FSQL[RefreshSQLIndex].Text :=
    'select' + SLineBreak +
    '  ' + SelectFields + SLineBreak +
    'from' + SLineBreak +
    '  ' + TableName + SLineBreak +
    'where' + SLineBreak +
    '  ' + RefreshWhereSQL;
    
  PageControl.ActivePage := tabsheetSQL;
end;

procedure TIfxFormUpdateSQL.radiogrpStatementTypeClick(Sender: TObject);
begin
  memoSQL.OnChange := nil;
  memoSQL.Lines.Assign(FSQL[radiogrpStatementType.ItemIndex]);
  memoSQL.OnChange := memoSQLChange;
end;

procedure TIfxFormUpdateSQL.memoSQLChange(Sender: TObject);
begin
  FSQL[radiogrpStatementType.ItemIndex].Assign(memoSQL.Lines);
end;

procedure TIfxFormUpdateSQL.ActionListUpdate(Action: TBasicAction;
  var Handled: Boolean);
begin
  actionTableDefinitions.Enabled := (FConnection <> nil) and (comboTableName.Text <> '');
  actionDataSetDefaults.Enabled := FDataSet <> nil;
  actionGenerateSQL.Enabled := comboTableName.Text <> '';
end;


end.